package com.stms.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.stms.bean.Student;
import com.stms.utils.DBUtil;

public class StudentDao {

	public List<Student> selectAllStudent(){
		Connection connection = DBUtil.getConnection();
		try {
			PreparedStatement statement = connection.prepareStatement("select * from student");
			ResultSet set = statement.executeQuery();
			List<Student> lstStudents = new ArrayList<>();
			while(set.next()){
				Student student = new Student();
				student.setId(set.getInt("id"));
				student.setStuName(set.getString("stuName"));
				student.setClassName(set.getString("className"));
				student.setStuNo(set.getString("stuNo"));
				student.setSex(set.getString("sex"));
				student.setAge(set.getInt("age"));
				student.setAddr(set.getString("addr"));
				lstStudents.add(student);
			}
			return lstStudents;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public int addStudent(Student student) {
		Connection connection = DBUtil.getConnection();
		try {
			PreparedStatement statement = connection.prepareStatement("insert into student(stuName,className,stuNo,sex,age,addr) values(?,?,?,?,?,?)");
			statement.setString(1, student.getStuName());
			statement.setString(2, student.getClassName());
			statement.setString(3, student.getStuNo());
			statement.setString(4, student.getSex());
			statement.setInt(5, student.getAge());
			statement.setString(6, student.getAddr());
			return statement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	public Student selectStudentById(int id) {
		Student student = null;
		try  {
			Connection connection = DBUtil.getConnection();
			PreparedStatement statement = connection.prepareStatement("SELECT * FROM student WHERE id = ?");
			statement.setInt(1, id);
			try{
				ResultSet result = statement.executeQuery();
				if (result.next()) {
					student = new Student();
					student.setId(result.getInt("id"));
					student.setStuName(result.getString("stuName"));
					student.setClassName(result.getString("className"));
					student.setStuNo(result.getString("stuNo"));
					student.setSex(result.getString("sex"));
					student.setAge(result.getInt("age"));
					student.setAddr(result.getString("addr"));
				}
			}catch (SQLException e){
				e.printStackTrace();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return student;
	}

	public int updateStudent(Student student) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = DBUtil.getConnection();
			String sql = "UPDATE student SET stuName=?, className=?, stuNo=?, sex=?, age=?, addr=? WHERE id=?";
			statement = connection.prepareStatement(sql);
			statement.setString(1, student.getStuName());
			statement.setString(2, student.getClassName());
			statement.setString(3, student.getStuNo());
			statement.setString(4, student.getSex());
			statement.setInt(5, student.getAge());
			statement.setString(6, student.getAddr());
			statement.setInt(7, student.getId());
			return statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0; // 更新失败
	}

	public int deleteStudent(int id) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			// 获取数据库连接
			connection = DBUtil.getConnection();
			String sql = "DELETE FROM student WHERE id = ?";
			// 创建 PreparedStatement 对象
			statement = connection.prepareStatement(sql);
			// 设置参数
			statement.setInt(1, id);
			// 执行删除操作
			return statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0; // 删除失败
	}
}
